import xlwings as xw


class ExcelHandler:
    def __init__(self, excel_file, new_sheet):
        self.excel_file = excel_file
        self.new_sheet = new_sheet
        self.app = xw.App(visible=True, add_book=False)
        self.app.display_alerts = False
        self.app.screen_updating = False
        if excel_file:
            try:
                self.workbook = self.app.books.open(self.excel_file)
                # 获取工作簿中的所有sheet名字
                self.namelist = []
                self.sleet_num = self.workbook.sheets.count
                for i in range(0, self.sleet_num):
                    self.namelist.append(self.workbook.sheets[i].name)
                # 如果存在同名的sheet就删除新建
                for i in range(0, self.sleet_num):
                    if self.namelist[i] == self.new_sheet:
                        self.workbook.sheets[i].delete()
                self.workbook.sheets.add(self.new_sheet)
                self.sheet = self.workbook.sheets.active
            except Exception as e:
                print(e)
        else:
            self.workbook = None
            self.sheet = None

    def read_value(self, cell):
        if self.sheet:
            return self.sheet.range(cell).value

    def save_workbook(self):
        if self.workbook:
            self.workbook.save()

    def write_value(self, cell, value):
        if self.sheet:
            self.sheet.range(cell).value = value
        self.save_workbook()

    def clear_cell(self, range_str):
        if self.sheet:
            range_to_clear = self.sheet.range(range_str)
            range_to_clear.clear()

    def __del__(self):
        self.workbook.close()
        self.app.quit()


if __name__ == '__main__':
    xls_path = r'../sn-inventory.xlsx'
    handler = ExcelHandler(xls_path)
    handler.write_value('C3', "134567")
    print(handler.read_value("C3"))
